package com.sapient;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.sapient.Config;

/**
 * Servlet implementation class CommodityEdit
 */
@WebServlet("/CommodityEdit")
public class CommodityEdit extends HttpServlet {


	String[] result = null;
	String[] details = null;
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public CommodityEdit() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub		
		int rows = 0;
		String selected = request.getParameter("selected");
		ResultSet rs = null;
		Connection conn = null; 
		HttpSession session=request.getSession();
		String user = (String) session.getAttribute("role");

		try
		{ 
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(Config.url,Config.username,Config.password); 
			Statement st = conn.createStatement(); 
			String SQL = "";
			if(user.equals("user"))
				SQL = "select commoditytype from commodity where flag='N' and status='A'";
			else
				SQL = "select commoditytype from commodity where status='A'";

			rs= st.executeQuery(SQL);
			while(rs.next()){
				rows++;
			}
			rs= st.executeQuery(SQL);
			result = new String[rows];
			int count = 0;
			while(rs.next())
			{
				result[count] = rs.getString(1);
				count++; 
			}

			if(selected!=null){
				SQL = "select CommodityType,CommodityClass,StartDate,EndDate,lastupdateduser,lastupdateddate, flag"
						+ " from Commodity where status='A' and CommodityType = '"+selected+"'";
				rs=st.executeQuery(SQL);
				details = new String[7];
				while(rs.next())
				{
					details[0] = rs.getString(1);
					details[1] = rs.getString(2);
					details[2] = rs.getString(3).substring(0, 10);
					String endDate= rs.getString(4);
					System.out.println(endDate);
					if(endDate!=null)
						endDate = endDate.substring(0, 10);
					else
						endDate = "0000-00-00";
					details[3] = endDate;
					details[4] = rs.getString(5);
					details[5] = rs.getString(6);
					details[6] = rs.getString(7);
				}
			}
			conn.close(); 

		} catch (Exception e) { 
			System.err.println("Got an exception! "); 
			System.err.println(e.getMessage()); 
			e.printStackTrace();
		} 
		request.setAttribute("commodity", result);
		request.setAttribute("commodityDetails", details);
		RequestDispatcher m = getServletContext().getRequestDispatcher("/CommodityEdit.jsp");
		m.forward(request, response);

	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub

		String commodityName = request.getParameter("commodityName");
		String commodityClass = request.getParameter("commodityClass");
		String startDate=request.getParameter("startDate");
		String endDate=request.getParameter("endDate");
		String SelectFlag=request.getParameter("SelectFlag");
		String oldFlag=request.getParameter("oldFlag");
		HttpSession session=request.getSession();
		String user = (String) session.getAttribute("User");
		 String role = (String) session.getAttribute("role");
		
		details = new String[7];
		details[0]=commodityName;
		details[1]=commodityClass;
		details[2]=startDate.substring(0, 10);
		details[3]=endDate.substring(0, 10);
		details[6]=SelectFlag;
		String status = "";

		Connection conn = null;
		try
		{ 
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(Config.url,Config.username,Config.password); 
			Statement st = conn.createStatement(); 

			String currencyFlag = "";
			String locationFlag = "";
			String marketFlag = "";
			if(!oldFlag.equals(SelectFlag)){
				if(SelectFlag.equals("N")){ //Yes to No
					String SQL1 = "select currency,location,marketflag,marketid from market where commoditytype = '"+commodityName+"'";
					ResultSet rs = st.executeQuery(SQL1);
					String[][] mlist = null;
					int rows=0;
					while(rs.next()){
						rows++;
					}
					rs= st.executeQuery(SQL1);
					mlist = new String[rows][8];
					int count = 0;
					while(rs.next())
					{
						mlist[count][0] = rs.getString(1);//c
						mlist[count][1] = rs.getString(2);//l
						mlist[count][2] = rs.getString(3);//mflag
						mlist[count][3] = rs.getString(4);//mid
						count++; 
						System.out.println(rs.getString(1)+rs.getString(2)+rs.getString(3)+rs.getString(4));
					}
					
					
					for(String[] s : mlist){
						String SQL2 = "select c.flag from currency c where c.currencyname = '"+s[0]+"'";
						System.out.println(SQL2);
						ResultSet rs2 = st.executeQuery(SQL2);
						while(rs2.next()){
							currencyFlag = rs2.getString(1); //currency flag
							System.out.println("Currency flag: "+currencyFlag);
						}
						SQL2 = "select l.flag from location l where l.locationname = '"+s[1]+"'";
						rs2 = st.executeQuery(SQL2);
						while(rs2.next()){
							locationFlag = rs2.getString(1); //location flag
							System.out.println("Location flag: "+locationFlag);
						}
							marketFlag = s[2]; //market flag
							System.out.println("Market flag: "+marketFlag);
							
						if(currencyFlag.equals("N")&&locationFlag.equals("N")&&marketFlag.equals("N")){
							SQL2="update Market set flag = 'N' where marketid = "+s[3];
							System.out.println(SQL2);
							st.executeUpdate(SQL2);
						}
					}
					System.out.println(count);
				}
				else{ 
					String SQL2 = "update Market set flag = 'Y' where commodityType = '"+commodityName+"'";
					System.out.println(SQL2);
					st.executeUpdate(SQL2);
				}
			}
			String SQL ="";
			if(endDate.equals("")){
				 if(role.equals("user")){
				SQL = "update Commodity set  status='P', CommodityClass = '"+commodityClass+"'"+",StartDate= to_date('"+startDate+"','RRRR-MM-DD'), "
						+ "lastupdateduser = '"+user+"',lastupdateddate=CURRENT_DATE, flag='"+SelectFlag+"' where commodityType = '"+commodityName+"'";
				status = "Updates pending approval.";
				 }
				 else{
						SQL = "update Commodity set CommodityClass = '"+commodityClass+"'"+",StartDate= to_date('"+startDate+"','RRRR-MM-DD'), "
								+ "lastupdateduser = '"+user+"',lastupdateddate=CURRENT_DATE, flag='"+SelectFlag+"' where commodityType = '"+commodityName+"'";
						status = "Updates succeed.";
				 }
			}
			else{
				if(role.equals("user")){
				SQL = "update Commodity set status='P',  CommodityClass = '"+commodityClass+"'"+",StartDate= to_date('"+startDate+"','RRRR-MM-DD'), "
						+ "EndDate= to_date('"+endDate+"','RRRR-MM-DD')"+
						",lastupdateduser = '"+user+"',lastupdateddate=CURRENT_DATE, flag='"+SelectFlag+"' where commodityType = '"+commodityName+"'";
				status = "Updates pending approval.";
				}
				else{
					SQL = "update Commodity set  CommodityClass = '"+commodityClass+"'"+",StartDate= to_date('"+startDate+"','RRRR-MM-DD'), "
							+ "EndDate= to_date('"+endDate+"','RRRR-MM-DD')"+
							",lastupdateduser = '"+user+"',lastupdateddate=CURRENT_DATE, flag='"+SelectFlag+"' where commodityType = '"+commodityName+"'";
					status = "Updates succeed.";
				}
			}

				System.out.println(SQL); 					

				st.executeUpdate(SQL);
				status = "Updates pending approval.";

				conn.close(); 
			} catch (Exception e) { 
				System.err.println("Got an exception! "); 
				System.err.println(e.getMessage()); 
				e.printStackTrace();
			} 
			request.setAttribute("status", status);
			request.setAttribute("commodity", result);
			request.setAttribute("commodityDetails", details);
			RequestDispatcher r = getServletContext().getRequestDispatcher("/CommodityEdit.jsp");
			r.forward(request, response);
		}

	}
